A few days ago, we (Artem and Chris) were working with two different customers that had the same requirement: they needed to encrypt certain tables in their databases.
This is not uncommon. Encryption comes with a slight performance penalty, so many companies prefer not to encrypt their entire database. Instead, the best practice is to encrypt only the tables that contain sensitive information, so less-sensitive data can be accessed without the overhead the encryption creates.
But how do you actually set up a database with selective encryption by table? In this post, we’ll walk through how to set up a CockroachDB cluster that can encrypt your sensitive tables while storing other tables in plaintext.
To accomplish this, we’ll be creating multiple stores on our CockroachDB nodes, and then using CockroachDB’s locality attribute flags to specify which data needs to use the encrypted store. It’s a pretty straightforward process, and we’re going to walk through it step by step.
High-Level Steps:
- Create an Encryption Key (AES-128)
- Start a CockroachDB cluster with encrypted and unencrypted stores
- Create and assign personally-identifiable information (PII) tables to encrypted stores and non-PII tables to non-encrypted stores
- Verify that our encryption is set up correctly
Step 1: Create an encryption key
First, let’s assign some variables to manage this setup in a scriptable fashion. We’ll create a $storepath variable which will tell CockroachDB where to keep its data. We’ll also use a $keypath variable which will have the location of our encryption key.
export keypath="${PWD}/workdir/key"
export storepath="${PWD}/workdir/data"
mkdir -p "${keypath}"
mkdir -p "${storepath}"
Next we’ll create an AES-128 encryption key. This functionality is built into CockroachDB:
cockroach gen encryption-key -s 128 $keypath/aes-128.key
After running the above command, you’ll get a confirmation message that says your key has been created and lists its directory path:
successfully created AES-128 key: /Users/username/Documents/workdir/key/aes-128.key
Step 2: Start a CockroachDB cluster with encrypted and unencrypted stores
Next, let’s create a cluster with both an encrypted store and a plaintext store. The syntax for this is --store=path=${dir}/1e/data,attrs=encrypt and --store=path=${dir}/1o/data,attrs=open respectively. We’ll be building a three node cluster, so we’ll create both encrypted and unencrypted stores for each node.
The attributes at the end of the store creation are used when pinning a table to an encrypted store. Additionally, the encryption key we created in the prior step is referenced in the --enterprise-encryption flag as well.
cockroach start \
--insecure \
--store=path=$storepath/1e/data,attrs=encrypt \
--store=path=$storepath/1o/data,attrs=open \
--enterprise-encryption=path=$storepath/1e/data,key=$keypath/aes-128.key,old-key=plain \
--listen-addr=127.0.0.1 \
--port=26257 \
--http-port=8080 \
--locality=region=local,zone=local \
--join=127.0.0.1:26257 \
--background
cockroach start \
--insecure \
--store=path=$storepath/2e/data,attrs=encrypt \
--store=path=$storepath/2o/data,attrs=open \
--enterprise-encryption=path=$storepath/2e/data,key=$keypath/aes-128.key,old-key=plain \
--listen-addr=127.0.0.1 \
--port=26259 \
--http-port=8081 \
--locality=region=local,zone=local \
--join=127.0.0.1:26257 \
--background
cockroach start \
--insecure \
--store=path=$storepath/3e/data,attrs=encrypt \
--store=path=$storepath/3o/data,attrs=open \
--enterprise-encryption=path=$storepath/3e/data,key=$keypath/aes-128.key,old-key=plain \
--listen-addr=127.0.0.1 \
--port=26261 \
--http-port=8082 \
--locality=region=local,zone=local \
--join=127.0.0.1:26257 \
--background
Now that the cluster is created, let’s initialize it.
cockroach init --insecure
Running this command should generate a Cluster successfully initialized message. We’ve now created and initialized a three node CockroachDB cluster with encrypted and plaintext stores.
Now it’s time for the cool part!
Step 3: Create PII and non-PII tables and assign them to the corresponding stores
Let’s create PII and non-PII tables and put each of them in the proper encrypted or unencrypted stores.
First, we’ll create the pii table, and configure a zone constraint that restricts it to using stores with encrypt attribute. We’ll also insert some sample data so that we can confirm our setup later.
cockroach sql --insecure \
-e "create table pii (k int primary key, v string);" \
-e "alter table pii configure zone using constraints='[+encrypt]';" \
-e "insert into pii (k,v) values (1,'bob');"
Running the above command should return confirmation that we have created a table, configured a zone, and inserted one row into the table.
Now, let’s repeat the process for our non_pii table, except that this time, we’ll set it to use stores with the open attribute.
cockroach sql --insecure \
-e "create table non_pii (k int primary key, v string);" \
-e "alter table non_pii configure zone using constraints='[+open]';" \
-e "insert into non_pii (k,v) values (1,'bob');"
Once both tables are created, we can look at the ranges of the table to see if they have been moved to the encrypted store:
cockroach sql --insecure -e "SHOW ALL ZONE CONFIGURATIONS;"
Confirm the output has the right constraints. If everything has been set up correctly, you’ll see the following in the output from running the command above:
`TABLE defaultdb.public.pii | ALTER TABLE defaultdb.public.pii CONFIGURE ZONE USING | constraints = '[+encrypt]'`
`TABLE defaultdb.public.non_pii | ALTER TABLE defaultdb.public.non_pii CONFIGURE ZONE USING | constraints = '[+open]'`
Note that the pii table does have the encrypt constraint and the non_pii table has the open constraint. That’s exactly what we should see, but let’s take the extra step of verifying everything just to be sure!
Step 4: Verify that our encryption is set up correctly
Verifying that the tables are in the right place requires us to query some of the internal metadata in CockroachDB. Specifically, we need to do the following:
- Find the table ranges
- Find the encrypted and unencrypted store IDs
- Confirm that the ranges from the tables are mapped to the correct stores
Previously in this tutorial we’ve been executing SQL commands via bash, but for this, let’s start a session in CockroachDB’s built-in SQL shell to run our commands. Run the following command in the terminal to open CockroachDB’s SQL shell:
cockroach sql --insecure
Step 4a: Find the table ranges
Recall that data in CockroachDB is broken up into ranges, which are then replicated across multiple nodes. We want to figure out what the range_id is for our pii table, and we can accomplish that using the SHOW RANGES command:
SELECT range_id, replicas FROM [SHOW RANGES FROM TABLE pii];
Running that command will generate output that shows the range_id for pii table:
range_id | replicas
-----------+-----------
37 | {1,3,5}
As we can see, we have one range to verify (range_id = 37), and this range has three replicas (one for each node in our cluster).
Find the encrypted and unencrypted store IDs
Next, we’ll need to query an internal table called crdb_internal.kv_store_status to find out more about the stores being used on our cluster and what attributes they have.
SELECT node_id, store_id, attrs
FROM crdb_internal.kv_store_status;
The above command will return the following output:
node_id | store_id | attrs
----------+----------+--------------
1 | 1 | ["encrypt"]
1 | 2 | ["open"]
2 | 3 | ["encrypt"]
2 | 4 | ["open"]
3 | 5 | ["encrypt"]
3 | 6 | ["open"]
We can see that there are six stores in total. Three of them have an [“encrypt”] attribute and three have an [“open”] attribute. Each node has one encrypted store and one unencrypted store.
This is all expected – it’s what we set up during step 2! Now we need to make sure that the data from our tables is being stored correctly, and we can do this by checking how the table ranges are mapped to these stores.
Step 4b: Confirm that the table ranges are mapped to the correct stores
Finally, we’re going to generate a table that shows the range_id for our pii table and what store_ids it corresponds with. Recall from the previous steps that the range ID for that table is 37 and the store IDs for the encrypted stores are 1, 3, and 5, so if everything is working as expected, we’ll see a table that shows range 37 is being stored in those three stores.
We can generate that output and confirm that the ranges from the tables are mapped to the correct stores using the query below.
This query is a bit more complex than the others we’ve used in this post, but it will get us to the answer we need. Essentially, we’re querying some of the CockroachDB metadata to pull range_id, node_id, and store_id for the pii table.
SELECT range_id, node_id, repls.store_id
FROM
(
SELECT range_id, unnest(replicas) AS store_id
FROM crdb_internal.ranges_no_leases
WHERE table_name = 'pii'
) AS repls
JOIN crdb_internal.kv_store_status AS ss ON (ss.store_id = repls.store_id)
ORDER BY range_id;
As expected, when we run this query we can see that range 37, which is our table with personally-identifiable information (PII), is assigned to stores 1, 3, and 5, which are the three encrypted stores we created:
range_id | node_id | store_id
-----------+---------+-----------
37 | 1 | 1
37 | 2 | 3
37 | 3 | 5
(3 rows)
We can repeat the same process to confirm that the unencrypted table non_pii is assigned to the unencrypted stores:
Find the range ID for the non_pii table:
SELECT range_id, replicas FROM [SHOW RANGES FROM TABLE non_pii];
range_id | replicas
-----------+-----------
38 | {2,4,6}
Check which stores the non_pii table is assigned to:
SELECT range_id, node_id, repls.store_id
FROM
(
SELECT range_id, unnest(replicas) AS store_id
FROM crdb_internal.ranges_no_leases
WHERE table_name = 'non_pii'
) AS repls
JOIN crdb_internal.kv_store_status AS ss ON (ss.store_id = repls.store_id)
ORDER BY range_id;
range_id | node_id | store_id
-----------+---------+-----------
38 | 1 | 2
38 | 2 | 4
38 | 3 | 6
(3 rows)
Again, we can see that the range with range_id 38 is stored on stores 2, 4 and 6! In other words, we did it – we’ve set up encryption at rest for our pii table but not for our non_pii table.
This gives us the best of both worlds – secure encryption for the tables that need it, and plaintext performance for the tables that don’t!
Step 5 (Optional): Cleanup
If you’ve been following along with this tutorial, you can run the following command to kill the CockroachDB process and remove the directories we created above.
pkill -9 cockroach
rm -Rf "${keypath}"
rm -Rf "${storepath}"
For more information about encryption in CockroachDB, please refer to our documentation, and check out our free courses for help getting your CockroachDB or CockroachDB Dedicated up and running!
Disclaimer: this tutorial is purposefully using an insecure test instance of CockroachDB which would not prevent unauthorized access to the data, regardless of the table encryption. For real world production scenarios, you’ll want to set up a secure cluster. Additionally, be aware that this kind of encryption at rest will not prevent data access for admin users and the default root user.
